SELECT convert(varchar,getdate())    -- Mar 15 2018 10:35AM

SELECT convert(varchar,getdate(),0)  -- Mar 15 2018 10:35AM

SELECT convert(varchar,getdate(),1)  -- 03/15/18

SELECT convert(varchar,getdate(),2)  -- 18.03.15

SELECT convert(varchar,getdate(),3)  -- 15/03/18

SELECT convert(varchar,getdate(),4)  -- 15.03.18

SELECT convert(varchar,getdate(),5)  -- 15-03-18

SELECT convert(varchar,getdate(),6)  -- 15 Mar 18

SELECT convert(varchar,getdate(),7)  -- Mar 15, 18

SELECT convert(varchar,getdate(),8)  -- 10:39:39

SELECT convert(varchar,getdate(),9)  -- Mar 15 2018 10:39:48:373AM

SELECT convert(varchar,getdate(),10) -- 03-15-18

SELECT convert(varchar,getdate(),11) -- 18/03/15

SELECT convert(varchar,getdate(),15) -- 180315

SELECT convert(varchar,getdate(),13) -- 15 Mar 2018 10:41:07:590

SELECT convert(varchar,getdate(),14) -- 10:41:25:903

SELECT convert(varchar,getdate(),20) -- 2018-03-15 10:43:56

SELECT convert(varchar,getdate(),21) -- 2018-03-15 10:44:04.950

SELECT convert(varchar,getdate(),22) -- 03/15/18 10:44:50 AM

SELECT convert(varchar,getdate(),23) -- 2018-03-15

SELECT convert(varchar,getdate(),24) -- 10:45:45

SELECT convert(varchar,getdate(),25) -- 2018-03-15 10:46:11.263

-- T-SQL with century (YYYY or CCYY) datetime styles (formats)

SELECT convert(varchar, getdate(), 100) -- Oct 23 2016 10:22AM (or PM)
SELECT convert(varchar, getdate(), 101) -- 10/23/2016

SELECT convert(varchar, getdate(), 102) -- 2016.10.23

SELECT convert(varchar, getdate(), 103) -- 23/10/2016

SELECT convert(varchar, getdate(), 104) -- 23.10.2016

SELECT convert(varchar, getdate(), 105) -- 23-10-2016

SELECT convert(varchar, getdate(), 106) -- 23 Oct 2016

SELECT convert(varchar, getdate(), 107) -- Oct 23, 2016

SELECT convert(varchar, getdate(), 108) -- 09:10:34

SELECT convert(varchar, getdate(), 109) -- Oct 23 2016 11:10:33:993AM (or PM)

SELECT convert(varchar, getdate(), 110) -- 10-23-2016

SELECT convert(varchar, getdate(), 111) -- 2016/10/23

SELECT convert(varchar, getdate(), 112) -- 20161023

SELECT convert(varchar, getdate(), 113) -- 23 Oct 2016 06:10:55:383

SELECT convert(varchar, getdate(), 114) -- 06:10:55:383(24h)

SELECT convert(varchar, getdate(), 120) -- 2016-10-23 06:10:55(24h)

SELECT convert(varchar, getdate(), 121) -- 2016-10-23 06:10:55.383

SELECT convert(varchar, getdate(), 126) -- 2016-10-23T06:10:55.383

GO

-- SQL cast string to datetime - time part 0 - sql hh mm

-- SQL Server cast string to DATE (SQL Server 2008 feature) - sql yyyy mm dd

SELECT [Date] =     CAST('20120228' AS date)         -- 2012-02-28

SELECT [Datetime] = CAST('20120228' AS datetime)      -- 2012-02-28 00:00:00.000

SELECT [Datetime] = CAST('20120228' AS smalldatetime) -- 2012-02-28 00:00:00

 

-- SQL convert string to datetime - time part 0

-- SQL Server convert string to date - sql times format

SELECT [Datetime] = CONVERT(datetime,'2010-02-28')

SELECT [Datetime] = CONVERT(smalldatetime,'2010-02-28')

 

SELECT [Datetime] = CAST('Mar 15, 2010' AS datetime)

SELECT [Datetime] = CAST('Mar 15, 2010' AS smalldatetime)

 

SELECT [Datetime] = CONVERT(datetime,'Mar 15, 2010')

SELECT [Datetime] = CONVERT(smalldatetime,'Mar 15, 2010')

 

SELECT [Datetime] = CAST('Mar 15, 2010 12:07:34.444' AS datetime)

SELECT [Datetime] = CAST('Mar 15, 2010 12:07:34.444' AS smalldatetime)

 

SELECT [Datetime] = CONVERT(datetime,'Mar 15, 2010 12:07:34.444')

SELECT [Datetime] = CONVERT(smalldatetime,'Mar 15, 2010 12:07:34.444')

 

SELECT [Datetime] = CAST('2010-02-28 12:07:34.444' AS datetime)

SELECT [Datetime] = CAST('2010-02-28 12:07:34.444' AS smalldatetime)

 

SELECT [Datetime] = CONVERT(datetime,'2010-02-28 12:07:34.444')

SELECT [Datetime] = CONVERT(smalldatetime,'2010-02-28 12:07:34.444')

 

-- Double conversion

SELECT [Datetime] = CAST(CAST(getdate() AS VARCHAR) AS datetime)

SELECT [Datetime] = CAST(CAST(getdate() AS VARCHAR) AS smalldatetime)

 

SELECT [Datetime] = CONVERT(datetime,convert(varchar,getdate()))

SELECT [Datetime] = CONVERT(smalldatetime,convert(varchar,getdate()))

------------